package weibo.database;

import java.sql.*;
import java.util.*;
import weibo.database.ConnectionTools;
import weibo.model.*;

/*
 *  管理员数据查询
 */

public class ManagerData {
	private Connection conn = null;

	/*
	 * 获取连接
	 */
	public ManagerData() {
		ConnectionTools db = new ConnectionTools();
		conn = db.getConn();
	}
	/*
	 * 析构函数
	 */
	public void finalize(){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
    }
	
	/*
	 * 管理员登录
	 */
	public boolean Login(String username,String password){
		String sql="select COUNT(*) as count FROM manager WHERE manager_loginname=? and manager_password=MD5(?)";
		try {
			java.sql.PreparedStatement exec=conn.prepareStatement(sql);
			exec.setString(1, username);
			exec.setString(2, password);
			ResultSet res=exec.executeQuery();
			res.next();
			int result=res.getInt("count");
			if (result==1){
				return true;
			}

			return false;
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			
			return false;
		}
	}
	/*
	 * 获取用户数，点赞数，推文数量
	 */
	public int[] QueryTotal(){
		String sql="select  (SELECT COUNT(tweet_id) FROM tweet) as tweet, (SELECT COUNT(userid) FROM user) as user, (SELECT COUNT(uod_id) FROM up_or_down) as uod FROM tweet LIMIT 0,1";
		int arr[]=new int[3];
		try {
			java.sql.PreparedStatement exec=conn.prepareStatement(sql);
			ResultSet res=exec.executeQuery();
			res.next();
			arr[0]=res.getInt(1);//推文数，
			arr[1]=res.getInt(2);//用户数
			arr[2]=res.getInt(3);//点赞数
			return arr;
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			return arr;
		}
	}
	/*
	 * 查询近七天用户数量变化统计
	 */
	public List<TotalMod> QueryUserTotal(){
		String sql="SELECT count(user.create_date) as user_count,user.create_date FROM user GROUP BY user.create_date ORDER BY user.create_date DESC LIMIT 0,7";
		List<TotalMod> Mod=new ArrayList<TotalMod>();
		try {
			java.sql.PreparedStatement exec=conn.prepareStatement(sql);
			ResultSet res=exec.executeQuery();
			while(res.next()){
				Mod.add(new TotalMod(
						res.getInt(1),
						res.getString(2)
						));
			}
			return Mod;
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			return Mod;
		}
	}
	/*
	 * 查询近七天Tweet数量变化统计
	 */
	public List<TotalMod> QueryTweetTotal(){
		String sql="select COUNT(*) as tweet_count,DATE(tweet.tweet_createdate) as tweet_date FROM tweet GROUP BY  DATE(tweet.tweet_createdate) ORDER BY tweet_date DESC LIMIT 0,7";
		List<TotalMod> Mod=new ArrayList<TotalMod>();
		try {
			java.sql.PreparedStatement exec=conn.prepareStatement(sql);
			ResultSet res=exec.executeQuery();
			while(res.next()){
				Mod.add(new TotalMod(
						res.getInt(1),
						res.getString(2)
						));
			}
			return Mod;
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			return Mod;
		}
	}
	/*
	 * 修改密码
	 */
	 public boolean ChangePassword(String managername,String password,String rollpassword){
		 String sql="update manager set manager_password=MD5(?) where manager_loginname=? and manager_password=MD5(?)";
			try {
				PreparedStatement exec=conn.prepareStatement(sql);
				exec.setString(1, password);
				exec.setString(2, managername);
				exec.setString(3, rollpassword);
				int result=exec.executeUpdate();
				if (result==1){
					conn.close();
					return true;
				}else if(result>1){
					conn.rollback();
					//更改太多异常，回滚
					conn.close();
					return false;
				}else{
					conn.close();
					return false;
				}
				
			} catch (SQLException e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
				return false;
			}
	 }
	 /*
	  * 释放连接
	  */
	 public void closeConn(){
		 try {
			this.conn.close();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	 }

	
}
